package dataBase;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.dom4j.DocumentException;

public class DbConnection {
	private String driver = null;
	private String URL = null;
	private String userName = null;
	private String userPwd = null;
	private Connection dbConn = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	// 配置数据库
	private void Configuration(String dataBase) throws DocumentException,
			Exception {
		driver = DbConfig.GetDbDriver(dataBase);
		URL = DbConfig.GetDbUrl(dataBase);
	}

	//
	public void DataBaseLoglin(String uname, String upwd) {
		userName = uname;
		userPwd = upwd;
	}

	public void Connection(String dataBase) throws DocumentException,
			SQLException, Exception {
		Configuration(dataBase);
		Class.forName(driver);
		DriverManager
				.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		dbConn = DriverManager.getConnection(URL, userName, userPwd);
		// System.out.println(dataBase + "  Database Connection Successful!");
		// // 如果连接成功
		// // 控制台输出Connection
		// // Successful!
	}

	public void Connection() throws DocumentException, SQLException, Exception {
		Class.forName(driver);
		DriverManager
				.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		dbConn = DriverManager.getConnection(URL, userName, userPwd);
		// System.out.println("Database Connection Successful!"); // 如果连接成功
		// // 控制台输出Connection
		// // Successful!
	}

	public DbConnection(String dataBase, String uname, String upwd)
			throws DocumentException, Exception {
		Configuration(dataBase);
		DataBaseLoglin(uname, upwd);
	}

	public DbConnection(String dataBase) throws DocumentException, Exception {
		Configuration(dataBase);
	}

	public DbConnection() {

	}

	public void CloseConnection() throws SQLException {
		if (resultSet != null)
			resultSet.close();
		if (statement != null)
			statement.close();
		if (preparedStatement != null)
			preparedStatement.close();
		if (dbConn != null)
			dbConn.close();
		// System.out.println("Database Close Successful!");
	}

	public Connection GetConnection() {
		return dbConn;
	}

	public ResultSet ExcuteStatement(String sql) throws SQLException {
		statement = dbConn.createStatement();
		resultSet = statement.executeQuery(sql);
		return resultSet;
	}

	public PreparedStatement GetPreparedStatement(String sql)
			throws SQLException {
		preparedStatement = dbConn.prepareStatement(sql);
		return preparedStatement;
	}

	public String GetCurrentDbType() throws SQLException {
		if (dbConn == null) {
			return null;
		} else {
			DatabaseMetaData dbmd = dbConn.getMetaData();
			String dbtp = dbmd.getDatabaseProductName();
			// System.out.println(dbtp);
			if (dbtp.equalsIgnoreCase("Microsoft SQL Server")) {
				return "MSSQL";
			}
			if (dbtp.equalsIgnoreCase("MySQL")) {
				return "MYSQL";
			}
			if (dbtp.equalsIgnoreCase("Oracle")) {
				return "ORACLE";
			}
			return dbtp;
		}
	}

	public int[] ExcuteTransaction(List<String> sql) throws SQLException {
		int[] num = null;
		try {
			statement = dbConn.createStatement();
			dbConn.setAutoCommit(false);
			for (int i = 0; i < sql.size(); i++) {
				statement.addBatch(sql.get(i));
			}
		} catch (Exception e) {
			System.out.println("数据库操作出错!!!");
		}
		try {
			num = statement.executeBatch();
			dbConn.commit();
		} catch (Exception e) {
			dbConn.rollback();
			System.out.println("插入出错！！！");
		}
		return num;
	}

	public static List<List<String>> GetImportKeysTableLists(
			DbConnection dbCon, List<String> tbList) throws SQLException {

		List<List<String>> importKeysTableLists = new ArrayList<List<String>>();
		DatabaseMetaData dbmd = dbCon.GetConnection().getMetaData();
		for (int i = 0; i < tbList.size(); i++) {
			List<String> importKeysTableList = new ArrayList<String>();
			ResultSet rs = dbmd.getImportedKeys(null, null, tbList.get(i));
			// importKeysTableList.add(tbList.get(i));
			while (rs.next()) {
				importKeysTableList.add(rs.getString("PKTABLE_NAME"));
				// System.out.println(rs.getString("PKTABLE_NAME"));
			}
			importKeysTableLists.add(importKeysTableList);
		}
		return importKeysTableLists;
	}

	public static void ImportKeyTest() throws DocumentException, Exception {
		DbConnection dbCon = new DbConnection("MSSQL_2", "sa", "123456");

		try {
			dbCon.Connection();
			DatabaseMetaData dbmd = dbCon.GetConnection().getMetaData();

//			ResultSet rs = dbmd.getTypeInfo();
			 ResultSet rs=dbmd.getProcedures(null, null, "TestProcedure");
			System.out.println();
			// ResultSet rs = dbmd.getImportedKeys(null, null, "sc3");

			ResultSetMetaData rsmd = rs.getMetaData();
			int num = rsmd.getColumnCount();
			for (int i = 1; i <= num; i++) {
				// 获取指定列的名称。
				System.out.print(rsmd.getColumnName(i) + "\t");
			}
			System.out.println();
			while (rs.next()) {
				for (int i = 1; i <= num; i++) {
					System.out.print(rs.getString(i) + "\t");
				}
				System.out.println();
			}
			String sql = "sp_helptext  TestProcedure";
			Statement st = dbCon.GetConnection().createStatement();
			rs = st.executeQuery(sql);
			rsmd = rs.getMetaData();
			num = rsmd.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= num; i++) {
					System.out.print(rs.getString(i) + "\t");
				}
				System.out.println();
			}

		} finally {
			System.out.println("for test");
			dbCon.CloseConnection();
		}
	}

	public static void main(String[] args) throws DocumentException,
			SQLException, Exception {
//		DbConnection dbCon = new DbConnection("ORACLE", "system", "root");
//		try {
//			dbCon.Connection();
//			String sql="select  *  from  user_tables where TABLESPACE_NAME='SYSTEM' ";
//			
//			Statement smt=dbCon.GetConnection().createStatement();
//			ResultSet rs=smt.executeQuery(sql);
//			ResultSetMetaData rsmd = rs.getMetaData();
//			int num = rsmd.getColumnCount();
//			for (int i = 1; i <= num; i++) {
//				// 获取指定列的名称。
//				System.out.print(rsmd.getColumnName(i) + "\t");
//			}
//			while (rs.next()) {
//				for (int i = 1; i <= num; i++) {
//					System.out.print(rs.getString(i) + "\t");
//				}
//				System.out.println();
//			}
//		} finally {
//			System.out.println("for test");
//			dbCon.CloseConnection();
//		}
//		DbConnection dbCon = new DbConnection("MSSQL", "sa", "123456");
		DbConnection dbCon = new DbConnection("MYSQL", "root", "root");
		dbCon.Connection();
		System.out.println(dbCon.GetConnection());
		dbCon.CloseConnection();
		
		
	}

}

// public void GetDatabaseMetaData() throws SQLException {
// DatabaseMetaData dbmd = dbConn.getMetaData();
// ResultSet rs = dbmd.getColumns(null, null, "TEST", null);
// ResultSetMetaData rsmd = rs.getMetaData();
// int cols = rsmd.getColumnCount();
// for (int i = 1; i <= cols; i++) {
// // 获取指定列的名称。
// System.out.print(rsmd.getColumnName(i) + "\t");
// }
// System.out.println("");
// while (rs.next()) {
// for (int i = 1; i <= cols; i++) {
// System.out.print(rs.getString(i) + "\t");
// }
// System.out.println("");
// // System.out.println(rs.getString(1) + "\t");
// }
// }